package seace.dao;

import java.io.UnsupportedEncodingException;
import java.nio.ByteBuffer;
import java.nio.CharBuffer;
import java.nio.charset.Charset;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import seace.controller.ControleDeConexao;
import seace.model.Participante;

public class ParticipanteDao {
	private Connection connection;

	public ParticipanteDao() throws SQLException {
		this.connection = ControleDeConexao.getConnection();
	}

	public void frequencia(Participante participante) throws SQLException {
		String sql = "INSERT INTO frequencias (presenca,inscritos_id,cronograma_atividades_id,hora) "
				+ "values (?,?,?,?)";

		PreparedStatement stmt = connection.prepareStatement(sql);

		stmt.setString(1, "1");
		stmt.setInt(2, participante.getInscricao());
		stmt.setInt(3, participante.getCronograma_Atividades());
		stmt.setTimestamp(4, new Timestamp(participante.getData().getTime()));
		stmt.execute();
		stmt.close();

	}

	public int getFrequencia(Participante participante) throws SQLException {
		String sql = "SELECT * FROM frequencias AS t1 WHERE t1.inscritos_id=? AND t1.cronograma_atividades_id=?";

		PreparedStatement stmt = connection.prepareStatement(sql);

		stmt.setInt(1, participante.getInscricao());
		stmt.setInt(2, participante.getCronograma_Atividades());

		ResultSet rs = stmt.executeQuery();

		int results = 0;
		
		while (rs.next()) {
			results++;
		}

		rs.close();
		stmt.close();
		return results;
	}

	public Participante getParticipante(Participante participante)
			throws SQLException {
		String sql = "SELECT t1.id, t2.nome FROM inscritos as t1, pessoas as t2 "
				+ "WHERE t2.cpf=t1.pessoa_cpf AND t1.id=?";

		PreparedStatement stmt = connection.prepareStatement(sql);

		stmt.setInt(1, participante.getInscricao());

		ResultSet rs = stmt.executeQuery();

		if (rs.next()) {
			participante.setInscricao(rs.getInt("id"));
			participante.setNome(rs.getString("nome"));
		} else {
			participante = null;
		}

		rs.close();
		stmt.close();

		return participante;
	}

	public Participante getParticipanteCompleto(Participante participante)
			throws SQLException {
		String sql = "SELECT t1.id, t2.nome, t1.pessoa_cpf, t3.nome as nomeminicurso "
				+ "FROM inscritos as t1, pessoas as t2, minicursos as t3,inscritos_minicursos as t4"
				+ "WHERE t2.cpf=t1.pessoa_cpf AND t4.minicursos_id=t3.id AND t4.inscritos_id=t1.id AND t1.id=?";

		PreparedStatement stmt = connection.prepareStatement(sql);

		stmt.setInt(1, participante.getInscricao());

		ResultSet rs = stmt.executeQuery();

		if (rs.next()) {
			participante.setInscricao(rs.getInt("id"));
			participante.setNome(rs.getString("nome"));
			participante.setCpf(rs.getString("pessoa_cpf"));

		} else {
			participante = null;
		}
		rs.close();
		stmt.close();

		return participante;
	}

	public List<Participante> getParticipantes() throws SQLException {
		String sql = "SELECT t1.id, t2.cpf, t2.nome FROM inscritos as t1, pessoas as t2 "
				+ "WHERE t2.cpf=t1.pessoa_cpf ORDER BY t2.nome ASC";

		PreparedStatement stmt = connection.prepareStatement(sql);

		ResultSet rs = stmt.executeQuery();

		List<Participante> participantes = new ArrayList<Participante>();

		while (rs.next()) {
			Participante participante = new Participante();
			participante.setInscricao(rs.getInt("id"));
			participante.setNome(rs.getString("nome"));
			participante.setCpf(rs.getString("cpf"));

			participantes.add(participante);
		}

		rs.close();
		stmt.close();

		return participantes;
	}
	public List<Participante> getUltimosDezParticipantes() throws SQLException, UnsupportedEncodingException {
		String sql = "SELECT t1.id, t2.nome,t3.id AS id_freq FROM inscritos AS t1, pessoas AS t2 , frequencias AS t3 " +
				"WHERE t3.inscritos_id=t1.id AND t1.pessoa_cpf = t2.cpf ORDER BY t3.id DESC LIMIT 10";
		
		PreparedStatement stmt = connection.prepareStatement(sql);

		ResultSet rs = stmt.executeQuery();

		List<Participante> participantes = new ArrayList<Participante>();

		while (rs.next()) {
			Participante participante = new Participante();
			participante.setInscricao(rs.getInt("id"));
			
			Charset utf8charset = Charset.forName("UTF-8");
			//Charset iso88591charset = Charset.forName("ISO-8859-1");
			//ByteBuffer inputBuffer = ByteBuffer.wrap(rs.getString("nome").getBytes());
			ByteBuffer inputBuffer = utf8charset.encode(rs.getString("nome"));
			CharBuffer data = utf8charset.decode(inputBuffer);
			
			participante.setNome(data.toString());

			participantes.add(participante);
		}

		rs.close();
		stmt.close();

		return participantes;
	}
	public void closeConection() throws SQLException {
		this.connection.close();
	}
}
